# coding=utf-8
import datetime
import random
import string
import time
import traceback

import requests
import json

import pymysql.cursors

from header import get_request_header

connect = pymysql.Connect(
    host='localhost',
    port=3306,
    user='root',
    passwd='root',
    db='jd',
    charset='utf8'
    # host = 'localhost',
    #        port = 3306,
    #               user = 'aegd',
    #                      passwd = 'aegd',
    #                               db = 'jd',
    #                                    charset = 'utf8'
)
if __name__ == '__main__':
    for j in range(1, 100):
        if(j==99):
            j=1
            print("将条数变成1，重新循环读取")
        starttime = datetime.datetime.now()
        updateNum = 0
        headers = get_request_header()
        # params 接收一个字典或者字符串的查询参数，字典类型自动转换为url编码，不需要urlencode()
        print(
            "https://xyq.cbg.163.com/cgi-bin/recommend.py?callback=Request.JSONP.request_map.request_1&_=1618250754847&act=recommd_by_role&page=" + str(
                j) + "&order_by=expire_time%20DESC&count=15&search_type=overall_search_role&view_loc=overall_search")
        url = "https://xyq.cbg.163.com/cgi-bin/recommend.py?callback=Request.JSONP.request_map.request_1&_=1618259122284&&act=recommd_by_role&page=" + str(
            j) + "&order_by=expire_time%20DESC&count=15&search_type=overall_search_role&view_loc=overall_search"
        response = requests.get(url, headers)
        # 查看响应内容，response.text 返回的是Unicode格式的数据
        # print response.text

        # 查看响应内容，response.content返回的字节流数据
        # print response.content
        dict_str = json.loads(response.content[36:-1].decode())
        if dict_str["status"]==2:
            print(dict_str)
            break
        # 查看完整url地址
        # print response.url
        # print dict_str['equips'][0]['max_expt_fashu']

        # for objectTemp in dict_str['equips']:
        # print len(objectTemp);
        for index in dict_str['equips']:
            objectTemp = index
            # SQL 查询语句
            selectSql = "SELECT count(1) FROM t_goods  WHERE eid = '%s'" % (str(objectTemp["eid"]))
            # 获取游标
            cursor = connect.cursor()
            try:
                # 执行SQL语句
                cursor.execute(selectSql)
                # 获取所有记录列表
                results = cursor.fetchall()
                cursor.close()
                result = results[0][0]
                if result != 0:
                    print(" have data")
                    updateSql = "UPDATE  t_goods SET`serverid` = " + str(
                        objectTemp["serverid"]) + ", `equip_level` = " + str(
                        objectTemp["equip_level"]) + ", `price` = " + str(
                        objectTemp["price"]) + ", `server_name` = '" + str(
                        objectTemp["server_name"]) + "', `highlights` = '" + json.dumps(objectTemp["highlights"],
                                                                                        indent=2).encode("utf-8").decode("unicode_escape") + "', `max_expt_gongji` = " + str(
                        objectTemp["max_expt_gongji"]) + ",`max_expt_fangyu` = " + str(
                        objectTemp["max_expt_fangyu"]) + ", `max_expt_fashu` = " + str(
                        objectTemp["max_expt_fashu"]) + ", `max_expt_kangfa` = " + str(
                        objectTemp["max_expt_kangfa"]) + ", `bb_expt_fangyu` = " + str(
                        objectTemp["bb_expt_fangyu"]) + ", `bb_expt_fashu` = " + str(
                        objectTemp["bb_expt_fashu"]) + ", `bb_expt_kangfa` = " + str(
                        objectTemp["bb_expt_kangfa"]) + ",`bb_expt_gongji` = " + str(
                        objectTemp["bb_expt_gongji"]) + ",`area_name` = '" + str(
                        objectTemp["area_name"]) + "',`level` = " + str(
                        objectTemp["level"]) + ",`selling_time` = '" + str(
                        objectTemp["selling_time"]) + "',`create_time` = '" + str(
                        objectTemp["create_time"]) + "',`equipid` = " + str(
                        objectTemp["equipid"]) + ",`server_sn` = " + str(
                        objectTemp["server_sn"]) + ",`price_int` = " + str(
                        objectTemp["price_int"]) + ",`seller_nickname` = '" + str(
                        objectTemp["seller_nickname"]) + "',`time_left` = '" + str(
                        objectTemp["time_left"]) + "',`update_time` = '" + time.strftime('%Y-%m-%d %H:%M:%S',
                                                                                         time.localtime(
                                                                                             time.time())) + "'  WHERE `eid` = '" + str(
                        objectTemp["eid"]) + "'"
                    # 获取游标
                    print(updateSql)
                    cursor = connect.cursor()
                    cursor.execute(updateSql)
                    cursor.close()
                    continue
                else:
                    insertSql = "INSERT INTO t_goods(`eid`, `serverid`, `equip_level`, `price`, `server_name`, `highlights`, `max_expt_gongji`, `max_expt_fangyu`, `max_expt_fashu`, `max_expt_kangfa`, `bb_expt_fangyu`, `bb_expt_fashu`, `bb_expt_kangfa`, `bb_expt_gongji`, `area_name`, `level`, `selling_time`, `create_time`, `equipid`, `server_sn`, `price_int`, `seller_nickname`, `time_left`, `insert_time`, `update_time`,`role_link`) VALUES ('" + str(
                        objectTemp["eid"]) + "', " + str(objectTemp["serverid"]) + ", " + str(
                        objectTemp["equip_level"]) + ", " + str(objectTemp["price"]) + ",'" + str(
                        objectTemp["server_name"]) + "', '" + json.dumps(objectTemp["highlights"],
                                                                         indent=2).encode("utf-8").decode("unicode_escape") + "',  " + str(
                        objectTemp["max_expt_gongji"]) + "," + str(objectTemp["max_expt_fangyu"]) + " , " + str(
                        objectTemp["max_expt_fashu"]) + ", " + str(objectTemp["max_expt_kangfa"]) + ", " + str(
                        objectTemp["bb_expt_fangyu"]) + ", " + str(objectTemp["bb_expt_fashu"]) + "," + str(
                        objectTemp["bb_expt_kangfa"]) + " ,  " + str(objectTemp["bb_expt_gongji"]) + " , '" + str(
                        objectTemp["area_name"]) + "', " + str(objectTemp["level"]) + ", '" + str(
                        objectTemp["selling_time"]) + "', '" + str(objectTemp["create_time"]) + "', " + str(
                        objectTemp["equipid"]) + ", '" + str(objectTemp["server_sn"]) + "',  " + str(
                        objectTemp["price_int"]) + " , '" + str(objectTemp["seller_nickname"]) + "', '" + str(
                        objectTemp["time_left"]) + "', '" + time.strftime('%Y-%m-%d %H:%M:%S',
                                                                          time.localtime(
                                                                              time.time())) + "','" + time.strftime(
                        '%Y-%m-%d %H:%M:%S', time.localtime(time.time())) + "','https://xyq.cbg.163.com/equip?s="+str(objectTemp["serverid"])+"&eid="+str(objectTemp["eid"])+"')"
                    # 获取游标
                    cursor = connect.cursor()
                    cursor.execute(insertSql)
                    connect.commit()
                    cursor.close()
                    updateNum = updateNum + 1
                    continue
                selectSql = "SELECT count(1) FROM t_goods_info  WHERE eid = '%s'" % (str(objectTemp["eid"]))
                # 获取游标
                cursor = connect.cursor()
                try:
                    # 执行SQL语句
                    cursor.execute(selectSql)
                    # 获取所有记录列表
                    results = cursor.fetchall()
                    cursor.close()
                    result = results[0][0]
                    if result != 0:
                        continue
                    else:
                        insertInfoSql = "INSERT INTO  t_goods_info (`qixue`,`max_expt_fashu`,`lingli`,`fair_show_end_time_left`,`speed`,`appointed_roleid`,`addon_minjie`,`server_name`,`highlights`,`magic_damage`,`equip_count`,`min_buyer_level`,`accept_bargain`,`area_name`,`expt_kangfa`,`max_expt_gongji`,`minjie`,`suit_skill`,`school`,`zongshang`,`level`,`fangyu`,`equip_name`,`magic_defense`,`gem_value`,`selling_time`,`xiang_qian_level`,`equip_level_desc`,`mingzhong`,`is_collect`,`equip_face_img`,`create_time`,`equipid`,`server_sn`,`anti_fengyin`,`rec_desc`,`tag_key`,`shanghai`,`seller_nickname`,`expt_gongji`,`expt_fangyu`,`max_expt_kangfa`,`addon_total`,`mofa`,`jinglian_level`,`price_int`,`gem_level`,`addon_skill_chance`,`time_left`,`storage_type`,`expt_fashu`,`collect_num`,`server_id`,`score`,`addon_effect_chance`,`kindid`,`bargain_info`,`addon_naili`,`hp`,`pass_fair_show`,`unit_price_desc`,`game_ordersn`,`fengyin`,`defense`,`eid`,`addon_fali`,`addon_status`,`tag`,`addon_lingli`,`addon_tizhi`,`damage`,`suit_effect`,`special_skill`,`equip_level`,`addon_liliang`,`status`,`equip_type`,`price`,`equip_type_desc`,`expire_time`,`serverid`,`icon`,`special_effect`,`seller_roleid`,`desc`,`other_info`)VALUES(" + str(
                            objectTemp["qixue"]) + "," + str(objectTemp["max_expt_fashu"]) + "," + str(
                            objectTemp["lingli"]) + ",'" + str(objectTemp["fair_show_end_time_left"]) + "'," + str(
                            objectTemp["speed"]) + ",'" + str(objectTemp["appointed_roleid"]) + "'," + str(
                            objectTemp["addon_minjie"]) + ",'" + str(objectTemp["server_name"]) + "','" + json.dumps(
                            objectTemp["highlights"], indent=2).encode("utf-8").decode("unicode_escape") + "'," + str(
                            objectTemp["magic_damage"]) + "," + str(
                            objectTemp["equip_count"]) + "," + str(objectTemp["min_buyer_level"]) + "," + str(
                            objectTemp["accept_bargain"]) + ",'" + str(objectTemp["area_name"]) + "'," + str(
                            objectTemp["expt_kangfa"]) + "," + str(objectTemp["max_expt_gongji"]) + "," + str(
                            objectTemp["minjie"]) + "," + str(objectTemp["suit_skill"]) + "," + str(
                            objectTemp["school"]) + "," + str(objectTemp["zongshang"]) + "," + str(
                            objectTemp["level"]) + "," + str(
                            objectTemp["fangyu"]) + "," + str(objectTemp["equip_name"]) + "," + str(
                            objectTemp["magic_defense"]) + "," + str(objectTemp["gem_value"]) + ",'" + str(
                            objectTemp["selling_time"]) + "'," + str(objectTemp["xiang_qian_level"]) + ",'" + str(
                            objectTemp["equip_level_desc"]) + "'," + str(objectTemp["mingzhong"]) + "," + str(
                            objectTemp["is_collect"]) + ",'" + str(objectTemp["equip_face_img"]) + "','" + str(
                            objectTemp["create_time"]) + "'," + str(objectTemp["equipid"]) + "," + str(
                            objectTemp["server_sn"]) + "," + str(objectTemp["anti_fengyin"]) + ",'" + str(
                            objectTemp["rec_desc"]) + "','" + json.dumps(
                            objectTemp["tag_key"], indent=2).encode("utf-8").decode("unicode_escape") + "'," + str(
                            objectTemp["shanghai"]) + ",'" + str(objectTemp["seller_nickname"]) + "'," + str(
                            objectTemp["expt_gongji"]) + "," + str(objectTemp["expt_fangyu"]) + "," + str(
                            objectTemp["max_expt_kangfa"]) + "," + str(objectTemp["addon_total"]) + "," + str(
                            objectTemp["mofa"]) + "," + str(objectTemp["jinglian_level"]) + "," + str(
                            objectTemp["price_int"]) + "," + str(objectTemp["gem_level"]) + "," + str(
                            objectTemp["addon_skill_chance"]) + ",'" + str(objectTemp["time_left"]) + "'," + str(
                            objectTemp["storage_type"]) + "," + str(objectTemp["expt_fashu"]) + "," + str(
                            objectTemp["collect_num"]) + "," + str(objectTemp["server_id"]) + "," + str(
                            objectTemp["score"]) + "," + str(objectTemp["addon_effect_chance"]) + "," + str(
                            objectTemp["kindid"]) + ",'" + json.dumps(
                            objectTemp["bargain_info"], indent=2) + "'," + str(
                            objectTemp["addon_naili"]) + "," + str(objectTemp["hp"]) + "," + str(
                            objectTemp["pass_fair_show"]) + ",'" + str(objectTemp["unit_price_desc"]) + "','" + str(
                            objectTemp["game_ordersn"]) + "'," + str(objectTemp["fengyin"]) + "," + str(
                            objectTemp["defense"]) + ",'" + str(objectTemp["eid"]) + "'," + str(
                            objectTemp["addon_fali"]) + ",'" + str(
                            objectTemp["addon_status"]) + "'," + json.dumps(
                            objectTemp["tag"], indent=2).encode("utf-8").decode("unicode_escape") + "," + str(
                            objectTemp["addon_lingli"]) + ",'" + str(objectTemp["addon_tizhi"]) + "','" + str(
                            objectTemp["damage"]) + "'," + str(objectTemp["suit_effect"]) + "," + str(
                            objectTemp["special_skill"]) + "," + str(objectTemp["equip_level"]) + "," + str(
                            objectTemp["addon_liliang"]) + "," + str(objectTemp["status"]) + "," + str(
                            objectTemp["equip_type"]) + "," + str(objectTemp["price"]) + ",'" + str(
                            objectTemp["equip_type_desc"]) + "','" + str(objectTemp["expire_time"]) + "'," + str(
                            objectTemp["serverid"]) + "," + str(objectTemp["icon"]) + "," + str(
                            objectTemp["special_effect"]) + "," + str(objectTemp["seller_roleid"]) + ",'" + str(
                            objectTemp["desc"]) + "','" + str(objectTemp["other_info"]) + "')"
                        # print(insertInfoSql)
                        # 获取游标
                        cursor = connect.cursor()
                        cursor.execute(insertInfoSql)
                        connect.commit()
                        cursor.close()
                        continue
                except Exception as e:
                    print('str(e):\t\t', str(e), cursor.execute(insertInfoSql))
            except Exception as e:
                print('str(Exception):\t', str(Exception))
                print('str(e):\t\t', str(e),cursor.execute(insertInfoSql))
                traceback.print_exc()
                print('traceback.format_exc():\n%s' % traceback.format_exc())
        endtime = datetime.datetime.now()
        time.sleep(random.randint(10, 20))
        print(str((endtime - starttime).total_seconds()) + '秒成功插入' + str(updateNum) + '条数据,------》间隔：' + str(
            (datetime.datetime.now() - starttime).seconds) + "秒")
        # 查看响应头部字符编码
        # print response.encoding
        # 查看响应码
        # print response.status_code
